/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package dataint;

import java.sql.*;
import java.util.*;

/**
 *
 * @author Karim Djelassi
 */
public class DataSource2
{
    private static final String dbUri = "jdbc:mysql://penelope.uni-muenster.de:3306/" +
            "dataint_source_2?useUnicode=true&characterEncoding=UTF8";
    private static final String dbUser = "studentWS09";
    private static final String dbPasswd = "1994Inmon";

    // Return customer list
    public LinkedList<Customer> getCustomerList()
    {
        // Connect to data source
        Connection co = this.establishDBConnection();

        // Return cleaned customer list
        return this.fetchData(co);
    }

    // Create conntection to data source
    private Connection establishDBConnection()
    {
        try
        {
            Class.forName("com.mysql.jdbc.Driver");
            return DriverManager.getConnection(dbUri, dbUser,
                    dbPasswd);
        }
        catch (Exception e)
        {
            System.err.println("Something went wrong while connecting to the " +
                    "database.");
            System.exit(-1);
            return null;
        }
    }

    // Fetch data from data source; Clean and store data in customer list
    private LinkedList<Customer> fetchData(Connection cn)
    {
        // Initialize customer list and DB objects
        LinkedList<Customer> cl = new LinkedList<Customer>();
        Statement st = null;
        ResultSet rs = null;

        // Build SQL query
        String query = "SELECT " +
                "c.First, c.Middle, c.Last, c.Street, c.City, c.Phone, " +
                "COUNT(o.id) as OrderCount, MAX(o.Date) as LastOrder, " +
                "SUM(p.PUnitPrice) AS TOV " +
                "FROM CustDetails AS c JOIN Orders AS o ON c.id = o.Cust " +
                "JOIN Products AS p ON o.Prod = p.PNo " +
                "GROUP BY c.id";

        try
        {
            // Execute query
            st = cn.createStatement();
            rs = st.executeQuery(query);

            while(rs.next())
            {
                // Create new customer
                Customer currentCustomer = new Customer();

                // Fill in customer information (transform when necessary)
                currentCustomer.setFirstName( // done
                        getCombinedFirstName(rs.getString("First"), rs.getString("Middle")));
                currentCustomer.setLastName(rs.getString("Last")); // done
                currentCustomer.setStreetAddress(rs.getString("Street")); // done
                currentCustomer.setCity(rs.getString("City")); // done
                currentCustomer.setPhoneNo( // done
                        getFormattedPhoneNumber(rs.getString("Phone")));
                currentCustomer.setEmail(""); // done
                currentCustomer.setPremium( // done
                        getPremiumFromOrderCount(Integer.parseInt(rs.getString("OrderCount"))));
                currentCustomer.setDate(rs.getString("LastOrder")); // done
                currentCustomer.setTOV( // done
                        Double.parseDouble(rs.getString("TOV")));

                // Add customer to customer List
                cl.add(currentCustomer);
            }
        }
        catch (Exception e)
        {
            System.err.println("Something went wrong while fetching the data.");
            e.printStackTrace();
            System.exit(-1);
        }
        finally
        {
            try { if( rs != null) rs.close(); } catch( Exception e ) {}
            try { if( st != null) st.close(); } catch( Exception e ) {}
            try { if( cn != null) cn.close(); } catch( Exception e ) {}
        }

        // Return customer list
        return cl;
    }

    // Combine first name and middle name into a single string
    private String getCombinedFirstName(String firstName, String middleName)
    {
        // Append middle name to first name if available
        if (middleName.length() > 0) return firstName + " " + middleName + ".";
        else return firstName;
    }

    // Format phone number
    private String getFormattedPhoneNumber(String phoneNumber)
    {
        // Replace first dash by space, delete remaining dashes
        return phoneNumber.replaceFirst("-", " ").replaceAll("-", "");
    }

    // Convert order count to premium status
    private String getPremiumFromOrderCount(int orderCount)
    {
        // Premium status is granted if customer ordered more than five items
        if (orderCount > 5) return "Yes";
        else return "No";
    }
}
